﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using JavaScript;

namespace SQL
{
    /// <summary>
    ///SQLHelper 的摘要说明
    /// </summary>
    public class SQLHelper
    {
        public SQLHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        public static bool IsExist(UserInfo userInfo)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();

            var isExist = from u in dc.UserInfos
                          where u.UserName.Equals(userInfo.UserName) && u.Password.Equals(userInfo.Password)
                          select u;
            if (isExist.Count().Equals(0))
            {
                return false;
            }
            else 
            {
                return true;
            }
            dc.Dispose();
        }
        public static int getGroupID(string userName) 
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            var res = from userinfo in dc.UserInfos
                      where userinfo.UserName.Equals(userName)
                      select userinfo;
            foreach (var ri in res)
            {
                return ri.Group_id;
            }
            return 0;
//             foreach (var ri in res)
//             {
//                 if (ri.Group_id == 1) return 1;
//             }
//             return 0;
            
        }
        public static int getUserID(string userName)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            var res = from userinfo in dc.UserInfos
                      where userinfo.UserName.Equals(userName)
                      select userinfo;
            foreach (var ri in res)
            {
                return ri.User_id;
            }
            return 0;

        }
        public static void Insert(UserInfo userInfo)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            try
            {
                dc.UserInfos.InsertOnSubmit(userInfo);
                dc.SubmitChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
            dc.Dispose();
        }
        public static void Insert(News news)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            try
            {
                dc.News.InsertOnSubmit(news);
                dc.SubmitChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
            dc.Dispose();
        }
        public static void Insert(NewsToCatalog newsToCatalog)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            try
            {
                dc.NewsToCatalogs.InsertOnSubmit(newsToCatalog);
                dc.SubmitChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
            dc.Dispose();
        }
        public static void Insert(Picture picture)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            try
            {
                dc.Pictures.InsertOnSubmit(picture);
                dc.SubmitChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
            dc.Dispose();
        }
        public static void Delete(int news_id)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            var delete_relation = from relation in dc.NewsToCatalogs
                                  where relation.News_id.Equals(news_id)
                                  select relation;
            try
            {
                dc.NewsToCatalogs.DeleteAllOnSubmit(delete_relation);
                dc.SubmitChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
 
            var delete_news = from news in dc.News
                              where news.News_id.Equals(news_id)
                              select news;
            try
            {
                dc.News.DeleteAllOnSubmit(delete_news);
                dc.SubmitChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
           dc.Dispose();
        }
        public static void DeleteUser(int user_id)
        {
            NewsDataClassesDataContext dc = new NewsDataClassesDataContext();
            
            var delete_user = from userinfo in dc.UserInfos
                              where userinfo.User_id.Equals(user_id)
                              select userinfo;
            try
            {
                dc.UserInfos.DeleteAllOnSubmit(delete_user);
                dc.SubmitChanges();
            }
            catch (Exception e) {
                throw e;
            }
            dc.Dispose();
        }
    }
}
